﻿#region usings

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

#endregion usings

namespace SimpleCodeGen
{
    /// <summary>
    /// Databse helper
    /// </summary>
    public class DatabaseHelper
    {
        /// <summary>
        /// Query Constant
        /// </summary>
        private const string _QUERY = " SELECT TOP 1 * FROM ";

        /// <summary>
        /// Gets the database servers.
        /// </summary>
        /// <returns></returns>
        public static IEnumerable<ComboboxItem> GetDatabaseServers()
        {
            var result = new List<ComboboxItem>();
            var datatable = SmoApplication.EnumAvailableSqlServers(false);
            if (datatable != null && datatable.Rows.Count > 0)
            {
                foreach (DataRow row in datatable.Rows)
                {
                    result.Add(new ComboboxItem() { Text = row["Name"].ToString(), Value = row["Name"].ToString() });
                }

                result = result.OrderBy(x => x.Text).ToList();
            }

            return result;
        }

        /// <summary>
        /// Connects this instance.
        /// </summary>
        /// <returns></returns>
        public static Database Connect(CustomParameter customParameter, ref string message)
        {
            Database database = null;
            if (customParameter != null && !string.IsNullOrEmpty(customParameter.DatabaseServerName))
            {
                var serverConnection = new ServerConnection(customParameter.DatabaseServerName);

                //First check type of Authentication
                if (!customParameter.DatabaseSQLAuthenticationMode)
                {
                    //Windows Authentication
                    serverConnection.LoginSecure = true;
                }
                else
                {
                    // SQL Authentication
                    serverConnection.LoginSecure = false;
                    serverConnection.Login = customParameter.DatabaseUserName;
                    serverConnection.Password = customParameter.DatabasePassword;
                }
                serverConnection.DatabaseName = customParameter.DatabaseName;
                var server = new Server(serverConnection);

                try
                {
                    // connect to server
                    server.ConnectionContext.Connect();

                    // get database
                    database = server.Databases[customParameter.DatabaseName];
                }
                catch (Exception exception)
                {
                    message = exception.Message;
                    database = null;
                }
            }

            return database;
        }

        /// <summary>
        /// Gets the databases.
        /// </summary>
        /// <param name="serverName">Name of the server.</param>
        /// <returns></returns>
        public static IEnumerable<string> GetDatabases(string serverName)
        {
            var result = new List<string>();
            if (!string.IsNullOrEmpty(serverName))
            {
                var server = new Server(serverName);
                try
                {
                    if (server.Databases != null && server.Databases.Count > 0)
                    {
                        foreach (Database database in server.Databases)
                        {
                            result.Add(database.Name);
                        }
                    }
                }
                catch (Exception)
                {
                }
            }
            return result;
        }

        /// <summary>
        /// Gets the columns.
        /// </summary>
        /// <param name="customParameter">The custom parameter.</param>
        /// <returns></returns>
        public static IEnumerable<CustomColumn> GetColumns(CustomParameter customParameter)
        {
            var primaryKeys = GetPrimaryKeys(customParameter);
            var columns = new List<CustomColumn>();

            string connetionString = null;
            SqlConnection sqlCnn;
            SqlCommand sqlCmd;
            string sql = null;

            connetionString = GetConnectionString(customParameter);
            sql = string.Format(" SELECT TOP 1 * FROM [{0}].[{1}]", customParameter.SchemaName, customParameter.TableName);

            sqlCnn = new SqlConnection(connetionString);
            try
            {
                sqlCnn.Open();
                sqlCmd = new SqlCommand(sql, sqlCnn);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                DataTable schemaTable = sqlReader.GetSchemaTable();

                foreach (DataRow row in schemaTable.Rows)
                {
                    var column = new CustomColumn();
                    column.ColumnName = row["ColumnName"].ToString();
                    column.DataType = row["DataType"].ToString();
                    column.IsIdentity = (bool)row["IsIdentity"];
                    column.IsRowVersion = (bool)row["IsRowVersion"];

                    //column.AllowDBNull = row["AllowDBNull"].ToString();
                    column.IsKey = IsPrimaryKey(primaryKeys, column.ColumnName);

                    columns.Add(column);
                }
                sqlReader.Close();
                sqlCmd.Dispose();
                sqlCnn.Close();
            }
            catch (Exception ex)
            {
            }

            return columns;
        }

        /// <summary>
        /// Determines whether [is primary key] [the specified primary keys].
        /// </summary>
        /// <param name="primaryKeys">The primary keys.</param>
        /// <param name="columnName">Name of the column.</param>
        /// <returns>
        ///   <c>true</c> if [is primary key] [the specified primary keys]; otherwise, <c>false</c>.
        /// </returns>
        private static bool IsPrimaryKey(IEnumerable<string> primaryKeys, string columnName)
        {
            var result = false;

            if (primaryKeys != null && primaryKeys.Count() > 0 && !string.IsNullOrEmpty(columnName))
            {
                result = primaryKeys.Contains(columnName);
            }

            return result;
        }

        /// <summary>
        /// Gets the primary keys.
        /// </summary>
        /// <param name="customParameter">The custom parameter.</param>
        /// <returns></returns>
        private static IEnumerable<string> GetPrimaryKeys(CustomParameter customParameter)
        {
            var primaryKeys = new List<string>();

            if (customParameter != null && !string.IsNullOrEmpty(customParameter.TableName) && !string.IsNullOrEmpty(customParameter.SchemaName))
            {
                using (var sqlCnn = new SqlConnection(GetConnectionString(customParameter)))
                {
                    SqlCommand sqlCmd = null;
                    string sql = null;

                    try
                    {
                        sqlCnn.Open();
                        sqlCmd = new SqlCommand(sql, sqlCnn);
                        sqlCmd.Parameters.AddWithValue("table_name", customParameter.TableName);
                        sqlCmd.Parameters.AddWithValue("table_owner ", customParameter.SchemaName);
                        sqlCmd.CommandType = CommandType.StoredProcedure;
                        sqlCmd.CommandText = "sp_pkeys";
                        SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                        while (sqlReader.Read())
                        {
                            primaryKeys.Add(sqlReader["COLUMN_NAME"].ToString());
                        }

                        sqlReader.Close();
                        sqlCmd.Dispose();
                        sqlCnn.Close();
                    }
                    catch (Exception ex)
                    {
                        //  MessageBox.Show("Error: " + ex.InnerException.Message);
                    }
                    finally
                    {
                        sqlCnn.Close();
                    }
                }
            }

            return primaryKeys;
        }

        /// <summary>
        /// Gets the connection string.
        /// </summary>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        private static string GetConnectionString(CustomParameter parameters)
        {
            string result = string.Empty;
            if (parameters != null)
            {
                if (parameters.DatabaseSQLAuthenticationMode)
                {
                    result = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", parameters.DatabaseServerName, parameters.DatabaseName, parameters.DatabaseUserName, parameters.DatabasePassword);
                }
                else
                {
                    result = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=true", parameters.DatabaseServerName, parameters.DatabaseName);
                }
            }

            return result;
        }
    }
}